package com.yusp75.myexam.data

import android.content.Context
import android.util.Log
import androidx.lifecycle.LiveData
import androidx.room.ColumnInfo
import androidx.room.Dao
import androidx.room.Database
import androidx.room.DatabaseView
import androidx.room.Delete
import androidx.room.Embedded
import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.Index
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.PrimaryKey
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Relation
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.Transaction
import androidx.room.Update
import androidx.sqlite.db.SimpleSQLiteQuery
import androidx.sqlite.db.SupportSQLiteQuery
import kotlinx.coroutines.flow.Flow
import androidx.paging.PagingSource


// 题集
@Entity(tableName = "my_book")
data class Book(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,

    val name: String,
    val author: String?,
    val releaseTime: String?,
    val version: String?,
    val comment: String?,
    val isSelected: Boolean,  // 使用中
    val createdAt: Long = System.currentTimeMillis(),
    val updatedAt: Long = System.currentTimeMillis()
)

// 返回统计数的数据类
data class BookWithCounts(
    @Embedded val book: Book,
    val judgeCount: Int,
    val singleChoiceCount: Int,
    val multiChoiceCount: Int
) {
    val totalQuestions: Int
        get() = judgeCount + singleChoiceCount + multiChoiceCount
}

@Dao
interface BookDao {
    @Query("SELECT * FROM my_book WHERE id = :id")
    fun getBookById(id: Int): Book?

    @Query("SELECT id FROM my_book WHERE name = :name LIMIT 1")
    suspend fun getIdByName(name: String): Int?

    @Query("SELECT * FROM my_book WHERE isSelected = 1 ORDER BY updatedAt desc limit 1  ")
    fun getSelectedBook(): Flow<Book?>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertBook(book: Book): Long

    @Delete
    fun delete(vararg book: Book)

    // 一对多查询：获取某本书的所有题目
    @Transaction
    @Query("SELECT * FROM my_book WHERE id = :bookId")
    suspend fun getBookWithQuestions(bookId: Int): BookWithQuestions

    // 指定书的问题数量
    @Query("SELECT COUNT(*) FROM my_book WHERE id = :bookId")
    suspend fun getCountOfQuestionsByBook(bookId: Int): Int

    // 多层级查询：获取某本书的练习记录（通过Question关联）
    @Query(
        """
        SELECT er.* FROM my_record er
        JOIN my_question q ON er.questionId = q.id
        WHERE q.bookId = :bookId
    """
    )
    fun getRecordsByBook(bookId: Int): Flow<List<ExerciseRecord>>

    // 删除指定书
    @Query("DELETE FROM my_question WHERE bookId = :bookId")
    suspend fun deleteQuestionsByBookId(bookId: Int)

    // 更新书信息注释
    @Query("UPDATE my_book SET comment = :comment WHERE id = :bookId")
    suspend fun updateBookComment(bookId: Int, comment: String)

    // 更新书选则
    @Query("UPDATE my_book SET isSelected = :isSelected WHERE id = :id")
    suspend fun updateBookSelection(id: Int, isSelected: Boolean)

    // 统计题目数

    // 复合查询（返回包含三类题型数量的数据类）
    @Transaction
    @Query(
        """
        SELECT my_book.*,
            (SELECT COUNT(*) FROM my_question 
             WHERE bookId = my_book.id AND questionType = '判断') AS judgeCount,
            (SELECT COUNT(*) FROM my_question 
             WHERE bookId = my_book.id AND questionType = '选择' AND multipleChoice = 0) AS singleChoiceCount,
            (SELECT COUNT(*) FROM my_question 
             WHERE bookId = my_book.id AND questionType = '选择' AND multipleChoice = 1) AS multiChoiceCount
        FROM my_book
    """
    )
    fun getAllBooks(): Flow<List<BookWithCounts>>
}

// 题目
// 题目带状态
data class QuestionWithStats(
    val id: Int,
    val questionType: String?,
    val content: String,
    val answer: String,
    val analysis: String?,
    val bookName: String,
    val correctCount: Int,
    val totalCount: Int,
    val favorite: Boolean?, // 收藏
    val lastExerciseTime: Int?
)

@Entity(
    tableName = "my_question",
    indices = [
        Index(value = ["id", "bookId"]),  // 复合索引
        Index(value = ["lastPracticed"])  // 时间索引
    ],
    foreignKeys = [ForeignKey(
        entity = Book::class,
        parentColumns = ["id"],
        childColumns = ["bookId"],
        onDelete = ForeignKey.CASCADE
    )]
)

data class Question(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,

    val questionIdx: Int,
    val bookId: Int,  // 外键
    val questionType: String?, // 题目类型
    val content: String, // 题干
    val answer: String, // 选择题选项或判断题(错对)
    val analysis: String?, // 答案解析
    val multipleChoice: Boolean?,

    val favorite: Boolean?, // 收藏
    val lastPracticed: Long = System.currentTimeMillis()
)


@Dao
interface QuestionDao {
    @Query("SELECT * FROM my_question")
    fun getAllQuestions(): PagingSource<Int, Question>  // test: 分页

    @Query("SELECT * FROM my_question WHERE id = :id")
    fun getQuestionById(id: Int): Question?

    // 分类计数
    data class QuestionTypeCount(
        val total: Int,
        val choiceCount: Int,
        val judgeCount: Int
    )

    @Query(
        """
    SELECT 
        COUNT(*) AS total,
        SUM(CASE WHEN questionType = '选择' THEN 1 ELSE 0 END) AS choiceCount,
        SUM(CASE WHEN questionType = '判断' THEN 1 ELSE 0 END) AS judgeCount
    FROM my_question
    """
    )
    fun getQuestionCountFlow(): Flow<QuestionTypeCount>

    @Query("SELECT * FROM my_question WHERE bookId = :bookId")
    fun getQuestionsByBook(bookId: Int): LiveData<List<Question>>

    // 更新：收藏
    @Query("UPDATE my_question SET favorite = :isFavorite WHERE id = :id")
    suspend fun updateFavoriteStatus(id: Int, isFavorite: Boolean)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertQuestion(question: Question)

    companion object {
        // 速览 filter
        // SELECT ...  FROM table1  [JOIN table2 ON ...]  -- JOIN 操作在此   WHERE ...  -- WHERE 过滤条件在 JOIN 之后
        fun buildFilterQuery(
            bookId: Int,
            isFavorite: Boolean? = null,
            onlyWrong: Boolean? = null
        ): SupportSQLiteQuery {
            val query = StringBuilder(
                """
                SELECT q.id, q.questionType, q.content, q.answer, q.analysis, q.favorite, 
                       b.name as bookName,
                       (SELECT COUNT(*) FROM my_record r WHERE r.questionId = q.id AND r.bookId = b.id AND r.isCorrect = 1) as correctCount,
                       (SELECT COUNT(*) FROM my_record r WHERE r.questionId = q.id AND r.bookId = b.id) as totalCount,
                       (SELECT MAX(r.createdAt) FROM my_record r WHERE r.questionId = q.id) as lastExerciseTime
                FROM my_question q
                JOIN my_book b ON q.bookId = b.id
                """
            )

            val conditions = mutableListOf<String>()
            conditions.add("q.bookId = $bookId")

            isFavorite?.let {
                conditions.add("q.favorite = $it")
            }

            onlyWrong?.let {
                if (it) {
                    conditions.add(
                        """
                EXISTS (
                    SELECT 1 FROM my_record r 
                    WHERE r.questionId = q.id 
                    AND r.bookId = b.id 
                    AND r.isCorrect = 0
                )
            """.trimIndent()
                    )
                }
            }

            if (conditions.isNotEmpty()) {
                query.append(" WHERE ").append(conditions.joinToString(" AND "))
            }

            return SimpleSQLiteQuery(query.toString())
        }


        // 随机做题filter
        fun buildRandomTypeFilterQuery(
            bookId: Int,
            size: Int,
            random: Boolean,
            questionType: String?
        ): SupportSQLiteQuery {
            val before60 = System.currentTimeMillis() - 3600000
            val query = StringBuilder(
                """
                WITH recent_exercises AS (
                    SELECT DISTINCT questionId, bookId
                    FROM my_record
                    WHERE bookId = $bookId 
                    AND createdAt < $before60
            ),
                candidate_questions AS (
                    SELECT q.*
                    FROM my_question q
                    LEFT JOIN recent_exercises re ON q.id = re.questionId AND q.bookId = re.bookId
                    WHERE q.bookId = $bookId 
                    AND (re.questionId IS NULL OR NOT EXISTS (
                        SELECT 1 FROM my_record mr 
                        WHERE mr.questionId = q.id 
                        AND mr.bookId = q.bookId
                        AND mr.createdAt < $before60
                ))
                ${questionType?.let { "AND q.questionType = '$it'" } ?: ""}
                ORDER BY q.id ASC
                ${if (random) ", RANDOM()" else ""}
                LIMIT ${size * 3}
            )
            SELECT * FROM candidate_questions
            ${if (random) "ORDER BY RANDOM()" else ""}
            LIMIT $size
            """
            )

            return SimpleSQLiteQuery(query.toString())
        }

        fun buildOrderWithTypeFilterQuery(
            bookId: Int,
            lastId: Int,
            size: Int,
            questionType: String?
        ): SupportSQLiteQuery {
            val query = StringBuilder(
                """
            SELECT * FROM my_question 
            WHERE bookId = $bookId AND id >= $lastId 
            ${questionType?.let { "AND questionType = '$it'" } ?: ""}
            ORDER BY id ASC 
            LIMIT $size
            """
            )

            return SimpleSQLiteQuery(query.toString())
        }
        // object
    }

    @Query(
        """
        SELECT q.*, b.name as bookName 
        FROM my_question q
        JOIN my_book b ON q.bookId = b.id
        WHERE q.id = :questionId
    """
    )
    suspend fun getQuestionWithBook(questionId: Int): QuestionWithBook

    // 统计某本书的正确率
    @Query(
        """
        SELECT COUNT(*) as total, 
               SUM(CASE WHEN er.isCorrect THEN 1 ELSE 0 END) as correctCount
        FROM my_record er
        WHERE er.bookId = :bookId
    """
    )
    suspend fun getBookAccuracy(bookId: Int): AccuracyStats

    // 游标分页
    // 基础游标分页（按主键顺序）
    @Query(
        """
        SELECT * FROM my_question 
        WHERE bookId = :bookId AND id > :lastId 
        ORDER BY id ASC 
        LIMIT :pageSize
    """
    )
    suspend fun getQuestionsSequentially(
        bookId: Int,
        lastId: Int,
        pageSize: Int
    ): List<Question>

    // QuestionRepository
    data class QuestionTypeResult(
        val questionType: String,
        val count: Int
    )

    @Query(
        """
        SELECT questionType, COUNT(*) as count
        FROM my_question 
        WHERE bookId = :bookId 
        GROUP BY questionType
    """
    )
    suspend fun getQuestionTypeCounts(bookId: Int): List<QuestionTypeResult>

    // 速览 fragment 1
    @Query(
        """
        SELECT q.id, q.questionType, q.content, q.answer, q.analysis,
               b.name as bookName,
               (SELECT COUNT(*) FROM my_record r WHERE r.questionId = q.id AND r.bookId= b.id AND r.isCorrect = 1) as correctCount,
               (SELECT COUNT(*) FROM my_record r WHERE r.questionId = q.id AND r.bookId= b.id) as totalCount,
               (SELECT MAX(r.createdAt) FROM my_record r WHERE r.questionId = q.id) as lastExerciseTime
        FROM my_question q
        JOIN my_book b ON q.bookId = b.id
    """
    )
    fun getPagedQuestions(): PagingSource<Int, QuestionWithStats>

    // 速览 fragment 2
    @RawQuery(observedEntities = [Book::class, Question::class, ExerciseRecord::class])
    fun queryFilterByBuilder(query: SupportSQLiteQuery): List<QuestionWithStats>

    // 练习: 顺序和类型
    @RawQuery(observedEntities = [Book::class, Question::class, ExerciseRecord::class])
    fun queryTypeOrderFilterByBuilder(query: SupportSQLiteQuery): List<Question>

    // 顺序取题
    @RawQuery(observedEntities = [Book::class, Question::class])
    suspend fun loadNextPage(query: SupportSQLiteQuery): List<Question>

    // 自适应取题
    @Transaction
    @Query(
        """
    WITH stats AS (SELECT * FROM QuestionStatsView),
    recent_questions AS (
        SELECT DISTINCT questionId, bookId
            FROM my_record
            WHERE bookId = :bookId
            AND createdAt < datetime('now', '-30 minutes')
    )
    SELECT * FROM (
        SELECT q.* FROM my_question q
        LEFT JOIN stats r ON q.id = r.questionId
        WHERE q.bookId = :bookId AND q.questionType = '选择'
        AND q.id NOT IN (SELECT questionId FROM recent_questions)
        ORDER BY COALESCE(r.correctRate, 1.0) ASC,
                 COALESCE(r.hesitateRate, 0.0) DESC,
                 RANDOM()
        LIMIT :numOfChoice
    )
    UNION ALL
    SELECT * FROM (
        SELECT q.* FROM my_question q
        LEFT JOIN stats r ON q.id = r.questionId
        WHERE q.bookId = :bookId AND q.questionType = '判断'
        AND q.id NOT IN (SELECT questionId FROM recent_questions)
        ORDER BY COALESCE(r.correctRate, 1.0) ASC,
                 COALESCE(r.hesitateRate, 0.0) DESC,
                 RANDOM()
        LIMIT :numOfJudge
    )
"""
    )
    suspend fun getAdaptedQuestions(
        bookId: Int,
        numOfChoice: Int = 20,
        numOfJudge: Int = 30
    ): List<Question>

    // 支持分批加载的接口
    @Query(
        """
        SELECT * FROM my_question 
        WHERE bookId = :bookId AND questionType = :type
        ORDER BY lastPracticed ASC 
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun loadByType(bookId: Int, type: String, limit: Int, offset: Int): List<Question>

    // 映射回答结果
    @Query("SELECT * FROM my_question WHERE bookId IN (:bookIds) AND id IN (:questionIds)")
    suspend fun getQuestionsByIds(bookIds: List<Int>, questionIds: List<Int>): List<Question>

    @Query(
        """
    SELECT MIN(id) as min, MAX(id) as max 
    FROM my_question 
    WHERE (:type IS NULL OR questionType = :type)
    """
    )
    suspend fun getQuestionBounds(type: String?): QuestionBounds

    // --- end: question dao ---
}

// 答题记录
@Entity(
    tableName = "my_record",
    indices = [
        Index(value = ["bookId"]),
        Index(value = ["questionId"])
    ],
    foreignKeys = [
        ForeignKey(
            entity = Question::class,
            parentColumns = ["id"],
            childColumns = ["questionId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Book::class,
            parentColumns = ["id"],
            childColumns = ["bookId"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class ExerciseRecord(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,

    val questionId: Int,  // 外键
    val bookId: Int,  // 外键
    val isCorrect: Boolean,
    val isHesitate: Boolean,
    val timeCost: Long,
    val createdAt: Long = System.currentTimeMillis()
)

// 数据类：统计
data class ExerciseStatistics(
    val questionType: String,
    val total: Int,
    val totalCorrect: Int,
    val totalWrong: Int,
    val choiceCount: Int,
    val judgeCount: Int
)

@Dao
interface ExerciseRecordDao {
    @Query("SELECT * FROM my_record WHERE isCorrect = :isCorrect LIMIT :limit OFFSET :offset")
    fun getExerciseRecords(isCorrect: Boolean, limit: Int, offset: Int): Flow<List<ExerciseRecord>>

    @Transaction
    @Insert
    fun insertRecord(record: ExerciseRecord)

    @Update(onConflict = OnConflictStrategy.REPLACE)
    fun update(record: ExerciseRecord)

    // 统计： 题目数，练习数
    @Query(
        "SELECT " +
                "q.questionType, " +
                "COUNT(*) AS total, " +
                "SUM(CASE WHEN er.isCorrect = 1 THEN 1 ELSE 0 END) AS totalCorrect, " +
                "SUM(CASE WHEN er.isCorrect = 0 THEN 1 ELSE 0 END) AS totalWrong, " +
                "SUM(CASE WHEN q.questionType = 1 THEN 1 ELSE 0 END) AS choiceCount, " +
                "SUM(CASE WHEN q.questionType = 2 THEN 1 ELSE 0 END) AS judgeCount " +
                "FROM my_record er " +
                "LEFT JOIN my_question q ON er.questionId = q.id " +
                "GROUP BY q.questionType"
    )
    fun getQuestionTypeStatistics(): Flow<ExerciseStatistics>

    // 移除： 已学会
    @Query("DELETE FROM my_record WHERE bookid = :bookId AND questionid = :questionId AND isCorrect = 0")
    suspend fun delRecord(bookId: Int, questionId: Int)
}

// 预聚合统计视图
@DatabaseView(
    """
    SELECT questionId, 
           AVG(CASE WHEN isCorrect THEN 1.0 ELSE 0.0 END) AS correctRate,
           AVG(CASE WHEN isHesitate THEN 1.0 ELSE 0.0 END) AS hesitateRate
    FROM my_record
    GROUP BY questionId
"""
)
data class QuestionStatsView(
    val questionId: Int,
    val correctRate: Double?,
    val hesitateRate: Double?
) {
    init {
        require(questionId > 0) { "Invalid question ID" }
    }
}

// 数据库
@Database(
    entities = [Book::class, Question::class, ExerciseRecord::class],
    views = [QuestionStatsView::class],
    version = 1,
    exportSchema = false
)
abstract class ExamDatabase : RoomDatabase() {
    abstract fun bookDao(): BookDao
    abstract fun questionDao(): QuestionDao
    abstract fun recordDao(): ExerciseRecordDao

    companion object {
        @Volatile
        private var instance: ExamDatabase? = null

        fun getDatabase(context: Context): ExamDatabase {
            return instance ?: synchronized(this) {
                instance ?: Room.databaseBuilder(
                    context.applicationContext,
                    ExamDatabase::class.java,
                    "my_exam.db"
                ).build().also { instance = it }
            }
        }
    }
}

// 关系类定义
data class BookWithQuestions(
    @Embedded val book: Book,
    @Relation(
        parentColumn = "id",
        entityColumn = "bookId"
    )
    val questions: List<Question>
)

data class QuestionWithBook(
    @Embedded val question: Question,
    @ColumnInfo(name = "bookName") val bookName: String
)

data class AccuracyStats(
    val total: Int,
    val correctCount: Int
)

// 数据边界
data class QuestionBounds(
    val min: Int,
    val max: Int
)

// 考试结果
@Entity(tableName = "exam_results")
data class ExamResult(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,

    val bookId: Int,
    val questionId: Int,
    val correctAnswer: String,
    val userAnswer: String,
    val isCorrect: Boolean = false,
    val timestamp: Long = System.currentTimeMillis()
)

@Dao
interface ExamResultDao {
    @Insert
    suspend fun insert(result: ExamResult)

    @Query("SELECT * FROM exam_results WHERE bookId = :bookId")
    suspend fun getByBook(bookId: Int): List<ExamResult>

    @Query("DELETE FROM exam_results")
    suspend fun clearAll()
}

@Database(
    entities = [ExamResult::class],
    version = 1,
    exportSchema = false
)
abstract class InMemoryDatabase : RoomDatabase() {
    abstract fun examResultDao(): ExamResultDao

    companion object {
        private var instance: InMemoryDatabase? = null

        fun getInstance(context: Context): InMemoryDatabase {
            return instance ?: synchronized(this) {
                Room.inMemoryDatabaseBuilder(
                    context.applicationContext,
                    InMemoryDatabase::class.java
                )
                    .fallbackToDestructiveMigration(false)
                    .build()
                    .also { instance = it }
            }
        }
    }
}
